﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.ord;
using Domain.Entity.search;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class Purchase_hRepository : IPurchase_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //新增进货单
        public async Task<int> AddPurchase_h(Purchase_h purchase_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ord_purchase_h](pur_code, pur_date, sup_id, sup_name, sup_linkman, sup_linkphone, pur_depId, pur_depName, pur_userId, pur_userName, pur_categories, pur_num, pur_total,  pur_status, create_name, create_time, remark)
                                values('{purchase_H.pur_code}','{purchase_H.pur_date}',{purchase_H.sup_id},'{purchase_H.sup_name}','{purchase_H.sup_linkman}','{purchase_H.sup_linkphone}',{purchase_H.pur_depId},'{purchase_H.pur_depName}',{purchase_H.pur_userId},'{purchase_H.pur_userName}',{purchase_H.pur_categories},{purchase_H.pur_num},'{purchase_H.pur_total}',{purchase_H.pur_status},'{purchase_H.create_name}','{purchase_H.create_time}','{purchase_H.remark}');
                                SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql); //返回当前数据的主键
            }
        }


        //审核进货单
        public async Task<int> ApproverPurchase_h(int purid, int status, string approvername, string approvertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_purchase_h] set pur_status={status},approver_name='{approvername}',approver_time='{approvertime}' where pur_id={purid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //删除进货单
        public async Task<int> DeletePurchase_h(int id)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_purchase_h] set is_delete=1 where pur_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //高级查询
        public async Task<FenYe<Purchase_h>> GetAdvancedPurchase_h(FenYe<Purchase_h> fenYe, string? code, string? starttotal, string? endtotal, string? startdate, string? enddate, string? startnum, string? endnum, int? status, int? sup, string? suplink, int? dep, int? user, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_purchase");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and pur_code like '%{code}%'";
                if (!string.IsNullOrEmpty(starttotal)|| !string.IsNullOrEmpty(endtotal))
                    sqlparam += $" and  pur_total between '{starttotal}' and '{endtotal}'";
                if (!string.IsNullOrEmpty(startdate) || !string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  pur_date between '{startdate}' and '{enddate}'";
                if (!string.IsNullOrEmpty(startnum) || !string.IsNullOrEmpty(endnum))
                    sqlparam += $" and  pur_num between '{startnum}' and '{endnum}'";
                if (status != null && status != 0)
                    sqlparam += $" and  pur_status={status}";
                if (sup != null && sup != 0)
                    sqlparam += $" and  sup_id={sup}";
                if (!string.IsNullOrEmpty(suplink))
                    sqlparam += $" and sup_linkman like '%{suplink}%'";
                if (dep != null && dep != 0)
                    sqlparam += $" and  pur_depId={dep}";
                if (user != null && user != 0)
                    sqlparam += $" and  pur_userId={user}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Purchase_h> list = sqlConnection.Query<Purchase_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //查询所有进货单据
        public async Task<List<Purchase_h>> GetAllPurchase_h()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select * from [dbo].[ord_purchase_h] where is_delete=0 and (pur_status=4 or pur_status=5  or pur_status=6 or pur_status=10)";
                return sqlConnection.Query<Purchase_h>(sql).ToList();
            }
        }


        //根据货品id查询进货记录
        public async Task<List<Purchase_h>> GetAllPurchase_hBypid(int procid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select h.pur_id,pur_code,pur_date,sup_name,sup_linkman,sup_linkphone,pur_depName,pur_userName,pur_num,pur_total,create_name,create_time,approver_name,approver_time from [dbo].[ord_purchase_d] d
                                inner join [dbo].[ord_purchase_h] h on h.pur_id=d.pur_id
                                where proc_id={procid}";
                return sqlConnection.Query<Purchase_h>(sql).ToList();
            }
        }


        //根据进货编号查询主键id和进货总数
        public async Task<Purchase_h> Getid(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select pur_id,pur_num,pur_instockOK_num,pur_instockNo_num from [dbo].[ord_purchase_h] where pur_code='{code}'";
                return await sqlConnection.QueryFirstOrDefaultAsync<Purchase_h>(sql);
            }
        }


        //分页查询进货单
        public async Task<FenYe<Purchase_h>> GetPurchase_h(FenYe<Purchase_h> fenYe, string? code, int? status, int? sup, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_purchase");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and pur_code like '%{code}%'";
                if (sup!=0&&sup!=null)
                    sqlparam += $" and sup_id ={sup}";
                if (status != null && status != 0)
                    sqlparam += $" and  pur_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  pur_date between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<Purchase_h> list = sqlConnection.Query<Purchase_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据id查询进货单
        public async Task<Purchase_h> GetPurchase_HById(int id)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_purchase_h] where pur_id={id}";
                return await sqlConnection.QueryFirstAsync<Purchase_h>(sql);
            }
        }


        //进货统计
        public async Task<PurchaseTJResult> Purchase_hTJ(string? timeType, string? code, int? sup, int? user, int? dep, string? starttime, string? endtime, int? page, int? pagesize)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                var where = " h.is_delete = 0 and pur_status=6";
                var parameters = new DynamicParameters();

                if (!string.IsNullOrEmpty(starttime) && !string.IsNullOrEmpty(endtime))
                {
                    where += " AND h.pur_date BETWEEN @starttime AND @endtime";
                    parameters.Add("@starttime", starttime);
                    parameters.Add("@endtime", endtime);
                }
                if (!string.IsNullOrEmpty(code))
                {
                    where += " AND h.pur_code LIKE @code";
                    parameters.Add("@code", $"%{code}%");
                }
                if (sup != null && sup != 0)
                {
                    where += " AND h.sup_id = @sup";
                    parameters.Add("@sup", sup);
                }
                if (user != null && user != 0)
                {
                    where += " AND h.pur_userId = @user";
                    parameters.Add("@user", user);
                }
                if (dep != null && dep != 0)
                {
                    where += " AND h.pur_depId = @dep";
                    parameters.Add("@dep", dep);
                }
                if (!string.IsNullOrEmpty(timeType))
                {
                    switch (timeType)
                    {
                        case "today":
                            where += " AND CONVERT(date, h.pur_date) = CONVERT(date, GETDATE())";
                            break;
                        case "yesterday":
                            where += " AND CONVERT(date, h.pur_date) = CONVERT(date, DATEADD(day, -1, GETDATE()))";
                            break;
                        case "week":
                            where += " AND DATEPART(week, h.pur_date) = DATEPART(week, GETDATE()) AND YEAR(h.pur_date) = YEAR(GETDATE())";
                            break;
                        case "lastWeek":
                            where += " AND DATEPART(week, h.pur_date) = DATEPART(week, DATEADD(week, -1, GETDATE()))"
                                  + " AND YEAR(h.pur_date) = YEAR(DATEADD(week, -1, GETDATE()))";
                            break;
                        case "month":
                            where += " AND MONTH(h.pur_date) = MONTH(GETDATE()) AND YEAR(h.pur_date) = YEAR(GETDATE())";
                            break;
                        case "lastMonth":
                            where += " AND MONTH(h.pur_date) = MONTH(DATEADD(month, -1, GETDATE()))"
                                  + " AND YEAR(h.pur_date) = YEAR(DATEADD(month, -1, GETDATE()))";
                            break;
                    }
                }

                int skip = ((page ?? 1) - 1) * (pagesize ?? 10);

                // 1. 查询总数（只查主单表）
                string countSql = $@"
                    SELECT COUNT(1)
                    FROM [dbo].[ord_purchase_h] h
                    WHERE {where}
                ";
                int total = await sqlConnection.ExecuteScalarAsync<int>(countSql, parameters);

                // 2. 查询统计（全量，不分页）
                string statSql = $@"
                    SELECT 
                        ISNULL(SUM(d.proc_pur_total), 0) AS totalAmount,
                        COUNT(DISTINCT h.sup_id) AS totalSupplier,
                        COUNT(DISTINCT h.pur_id) AS totalOrder
                    FROM [dbo].[ord_purchase_h] h
                    LEFT JOIN [dbo].[ord_purchase_d] d ON h.pur_id = d.pur_id
                    WHERE {where}
                ";
                var stat = await sqlConnection.QueryFirstOrDefaultAsync(statSql, parameters);

                // 3. 查询当前页数据
                string sql = $@"
                    SELECT h.pur_id, h.pur_code, h.pur_date, h.sup_name, h.pur_depName, h.pur_userName, 
                           d.purd_id, d.proc_name, p.proc_code, d.proc_pur_num, d.proc_pur_total
                    FROM [dbo].[ord_purchase_h] h
                    LEFT JOIN [dbo].[ord_purchase_d] d ON h.pur_id = d.pur_id
                    LEFT JOIN [dbo].[bs_product] p ON p.proc_id = d.proc_id
                    WHERE {where}
                    ORDER BY h.pur_id
                    OFFSET @skip ROWS FETCH NEXT @pagesize ROWS ONLY;
                ";

                parameters.Add("@skip", skip);
                parameters.Add("@pagesize", pagesize ?? 10);

                var list = (await sqlConnection.QueryAsync<PurchaseDto>(sql, parameters)).ToList();

                return new PurchaseTJResult
                {
                    list = list,
                    total = total,
                    totalAmount = stat?.totalAmount ?? 0,
                    totalSupplier = stat?.totalSupplier ?? 0,
                    totalOrder = stat?.totalOrder ?? 0
                };
            }
        }


        //更新入库数量和单据状态
        public async Task<int> Updatenum(Purchase_h purchase_H)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection .OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_h] set pur_instockOK_num={purchase_H.pur_instockOK_num},pur_instockNO_num={purchase_H.pur_instockNO_num},pur_status={purchase_H.pur_status} where pur_id={purchase_H.pur_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改进货单
        public async Task<int> UpdatePurchase_h(Purchase_h purchase_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_h] set pur_date='{purchase_H.pur_date}',sup_id={purchase_H.sup_id},sup_name='{purchase_H.sup_name}',sup_linkman='{purchase_H.sup_linkman}',sup_linkphone='{purchase_H.sup_linkphone}',pur_depId={purchase_H.pur_depId},pur_depName='{purchase_H.pur_depName}',
                                pur_userId={purchase_H.pur_userId},pur_userName='{purchase_H.pur_userName}',pur_categories={purchase_H.pur_categories},pur_num={purchase_H.pur_num},pur_total='{purchase_H.pur_total}',pur_status={purchase_H.pur_status},update_name='{purchase_H.update_name}',update_time='{purchase_H.update_time}',remark='{purchase_H.remark}'
                                where pur_id={purchase_H.pur_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //更新已退货数量
        public async Task<int> UpdateRNum(int purid, int returnnum, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_h] set purd_returned_num={returnnum},pur_status={status} where pur_id={purid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        
        public async Task<int> UpdateRNum(int purid, int returnnum, int status, int newnum)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_h] set purd_returned_num={returnnum},pur_status={status},pur_num={newnum} where pur_id={purid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改单据状态
        public async Task<int> UpdateStatus(int purid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_purchase_h] set pur_status={status} where pur_id={purid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
